package com.rock.code.common.excel;

import com.rock.code.common.comm.CommDate;
import com.rock.code.common.comm.RandomGUID;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExportExcel {

    public String exportExcel(HttpServletRequest request, HttpServletResponse response, int sheetSize, String[] titles,
                              String[] fields, List<Object> rows, String className) throws Exception {
        HSSFWorkbook workbook = null;
        // 创建工作簿实例
        workbook = new HSSFWorkbook();
        int expRow = rows.size();
        int pageNum = ((expRow - 1) / sheetSize) + 1;
        int printCount = 0;
        for (int n = 0; n < pageNum; n++) {
            String sheetName = "第" + (n + 1) + "页";
            // 创建工作表实例
            HSSFSheet sheet = workbook.createSheet(sheetName);
            sheet.createFreezePane(0, 1);
            // 样式
            HSSFCellStyle titleStyle = workbook.createCellStyle();
            titleStyle = this.setPublic(titleStyle);
            titleStyle = this.setTitleFillColor(titleStyle);
            Font titleFont = this.createFonts(workbook, "黑体", HSSFColor.BLACK.index, false, (short) 220);
            titleStyle.setFont(titleFont);
            // 创建标题
            HSSFRow rowTitle = sheet.createRow(0);// 标题行
            rowTitle.setHeight((short) 350);
            for (int i = 0; i < titles.length; i++) {
                String title = titles[i];
                this.createCell(rowTitle, i, titleStyle, HSSFCell.CELL_TYPE_STRING, title);
            }
            // 创建数据行
            // 样式
            HSSFCellStyle dataStyle = workbook.createCellStyle();
            dataStyle = this.setPublic(dataStyle);
            Font dataFont = this.createFonts(workbook, "宋体", HSSFColor.BLACK.index, false, (short) 200);
            dataStyle.setFont(dataFont);
            for (int i = printCount; i < rows.size() && i < (n + 1) * sheetSize; i++) {
                Class<?> clazz = Class.forName(className);
                Object obj = rows.get(i);
                HSSFRow rowData = sheet.createRow(i + 1);// 标题行
                //rowData.setHeight((short)280);
                for (int j = 0; j < fields.length; j++) {
                    String field = fields[j];
                    PropertyDescriptor pd = new PropertyDescriptor(field, clazz);

                    Method rM = pd.getReadMethod();// 获得get方法
                    Object o = rM.invoke(obj);
                    if (o == null) {
                        o = "";
                    } else {
                        if (pd.getPropertyType() == Date.class) {
                            o = CommDate.dateToStr((Date) o, CommDate.YMD_L);
                        }
                    }
                    this.createCell(rowData, j, dataStyle, HSSFCell.CELL_TYPE_STRING, o);
                }
            }
            //for (int i = 0; i < titles.length; i++) {
            //    if (i == 0) {
            //        sheet.setColumnWidth(0, 0); // 主键列隐藏
            //    } else {
            //        sheet.autoSizeColumn(i);
            //    }
            //}
            printCount++;
        }
        String fileName = RandomGUID.getRandomGUID();
        // 生成xls文件名必须要是随机的，确保每个线程访问都产生不同的文件
        StringBuffer sb = new StringBuffer(fileName);
        String path = request.getSession().getServletContext().getRealPath("/");
        File file = new File(path + "xlsFile" + File.separator + sb.append(".xls").toString());
        if (!file.getParentFile().exists()) {
            file.getParentFile().mkdirs();
        }
        OutputStream out = new FileOutputStream(file);
        workbook.write(out);// 写入File
        out.flush();
        out.close();
        return file.getAbsolutePath();// 返回的是一个文件路径
    }

    public String exportExcel(HttpServletRequest request, HttpServletResponse response, int sheetSize, String[] titles,
                              String[] fields, List<HashMap> rows) throws Exception {
        HSSFWorkbook workbook = null;
        // 创建工作簿实例
        workbook = new HSSFWorkbook();
        int expRow = rows.size();
        int pageNum = ((expRow - 1) / sheetSize) + 1;
        int printCount = 0;
        for (int n = 0; n < pageNum; n++) {
            String sheetName = "第" + (n + 1) + "页";
            // 创建工作表实例
            HSSFSheet sheet = workbook.createSheet(sheetName);
            sheet.createFreezePane(0, 1);
            // 样式
            HSSFCellStyle titleStyle = workbook.createCellStyle();
            titleStyle = this.setPublic(titleStyle);
            titleStyle = this.setTitleFillColor(titleStyle);
            Font titleFont = this.createFonts(workbook, "黑体", HSSFColor.BLACK.index, false, (short) 220);
            titleStyle.setFont(titleFont);
            // 创建标题
            HSSFRow rowTitle = sheet.createRow(0);// 标题行
            rowTitle.setHeight((short) 350);
            for (int i = 0; i < titles.length; i++) {
                String title = titles[i];
                this.createCell(rowTitle, i, titleStyle, HSSFCell.CELL_TYPE_STRING, title);
            }
            // 创建数据行
            // 样式
            HSSFCellStyle dataStyle = workbook.createCellStyle();
            dataStyle = this.setPublic(dataStyle);
            Font dataFont = this.createFonts(workbook, "宋体", HSSFColor.BLACK.index, false, (short) 200);
            dataStyle.setFont(dataFont);
            for (int i = printCount; i < rows.size() && i < (n + 1) * sheetSize; i++) {
                //Class<?> clazz = Class.forName(className);
                Map<String, Object> obj = rows.get(i);
                HSSFRow rowData = sheet.createRow(i + 1);// 标题行
                //rowData.setHeight((short)280);
                int j = 0;
                Object o = null;
                for (j = 0; j < fields.length; j++) {
                    String field = fields[j];
                    for (Map.Entry<String, Object> entry : obj.entrySet()){
                        String key = entry.getKey();
                        String value = (String) entry.getValue();
                        if (key.equals(field)){
                            o = value;
                            this.createCell(rowData, j, dataStyle, HSSFCell.CELL_TYPE_STRING, o);
                            break;
                        } else {
                            o = "";
                            this.createCell(rowData, j, dataStyle, HSSFCell.CELL_TYPE_STRING, o);
                        }

                    }
                }
            }
            printCount++;
        }
        String fileName = RandomGUID.getRandomGUID();
        // 生成xls文件名必须要是随机的，确保每个线程访问都产生不同的文件
        StringBuffer sb = new StringBuffer(fileName);
        String path = request.getSession().getServletContext().getRealPath("/");
        File file = new File(path + "xlsFile" + File.separator + sb.append(".xls").toString());
        if (!file.getParentFile().exists()) {
            file.getParentFile().mkdirs();
        }
        OutputStream out = new FileOutputStream(file);
        workbook.write(out);// 写入File
        out.flush();
        out.close();
        return file.getAbsolutePath();// 返回的是一个文件路径
    }

    // 设置excel的title样式
    private HSSFCellStyle setPublic(HSSFCellStyle style) {
        // 设置一个单元格边框
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setBorderRight(CellStyle.BORDER_THIN);
        // 设置一个单元格边框颜色
        style.setRightBorderColor(HSSFColor.BLUE_GREY.index);
        style.setLeftBorderColor(HSSFColor.BLUE_GREY.index);
        style.setBottomBorderColor(HSSFColor.BLUE_GREY.index);
        style.setTopBorderColor(HSSFColor.BLUE_GREY.index);
        // 设置上下对齐方式
        style.setAlignment(CellStyle.ALIGN_CENTER);
        // 设置左右对齐方式
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        return style;
    }

    private HSSFCellStyle setTitleFillColor(HSSFCellStyle style) {
        // 设置颜色为黄色
        style.setFillForegroundColor(HSSFColor.AQUA.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        return style;
    }

    /**
     * 设置字体
     *
     * @param wb
     * @return
     */
    private Font createFonts(Workbook wb, String name, short color, boolean italic, short size) {
        // 创建Font对象
        Font font = wb.createFont();
        // 设置字体
        font.setFontName(name);
        // 着色
        font.setColor(color);
        // 斜体
        font.setItalic(italic);
        // 字体大小
        font.setFontHeight(size);
        return font;
    }

    // 创建Excel单元格
    private void createCell(HSSFRow row, int column, HSSFCellStyle style, int cellType, Object value) {
        HSSFCell cell = row.createCell(column);
        if (style != null) {
            cell.setCellStyle(style);
        }
        switch (cellType) {
            case HSSFCell.CELL_TYPE_STRING:
                cell.setCellValue(value.toString());
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(Double.parseDouble(value.toString()));
                break;
            default:
                break;
        }
    }

}
